/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.webaccess.retailws.DAO;

import com.webaccess.retailws.Bean.UserAddressBean;
import com.webaccess.retailws.Bean.UserInfoBean;
import com.webaccess.retailws.Util.EncryptDecryptUtil;
import com.webaccess.retailws.Util.UserAccountUtil;
import com.webaccess.retailws.Util.CommonUtil;
import com.webaccess.retailws.db.DBConnection;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 *
 * @author Owner
 */
public class UserInfoDAO {

    DBConnection dbc = null;
    Connection conn = null;
    ResultSet rs = null;
    String result = null;
    StringBuffer myQuery = null;
    EncryptDecryptUtil passwordEDUtil = null;
    UserAccountUtil userAccUtil = null;
    UserInfoBean userInfoBeanObj = null;
    UserAddressBean userAddrBeanObj = null;
    CommonUtil commUtil = null;
    CommonDAO commDAO = null;

    public UserInfoDAO() {
        try {
            dbc = new DBConnection();
            //conn = (Connection) dbc.getConnection();
            userAccUtil = new UserAccountUtil();
            commUtil = new CommonUtil();
            commDAO = new CommonDAO();
        } catch (Exception exception) {
            exception.printStackTrace();
        }
    }

    public Long insertUserInfo(UserInfoBean userInfoBeanObj, UserAddressBean[] userAddrBean) {
        Long userId = 0L, securityQuestionId = 0L, addressId = 0L;
        String rsValue = "";
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();

            myQuery.append("CALL sp_saveuserinfo(NULL,'");
            myQuery.append(userInfoBeanObj.getPrefix());
            myQuery.append("','");
            myQuery.append(userInfoBeanObj.getFirstName());
            myQuery.append("','");
            myQuery.append(userInfoBeanObj.getMiddleName());
            myQuery.append("','");
            myQuery.append(userInfoBeanObj.getLastName());
            myQuery.append("','");
            myQuery.append(userInfoBeanObj.getProfession());
            myQuery.append("','");
            myQuery.append(userInfoBeanObj.getEmailId());
            myQuery.append("','");
            myQuery.append(userInfoBeanObj.getDateOfBirth());
            myQuery.append("','");
            myQuery.append(userInfoBeanObj.getMobileNo());
            myQuery.append("','1','");
            myQuery.append(userInfoBeanObj.getUserName());
            myQuery.append("','");
            myQuery.append(userInfoBeanObj.getPassword());
            myQuery.append("','1','1','");
            myQuery.append(userInfoBeanObj.getNewsLetter());
            myQuery.append("','");
            myQuery.append(userInfoBeanObj.getInfoSaveEditDate());
            myQuery.append("','");
            myQuery.append(userInfoBeanObj.getInfoSaveEditDate());
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    while (rs.next()) {
                        userId = rs.getLong("sp_UserId");
                    }
                }
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_savesecurityquestion(NULL,'");
            myQuery.append(userId);
            myQuery.append("','");
            myQuery.append(userInfoBeanObj.getSecurityQuestionId());
            myQuery.append("','");
            myQuery.append(userInfoBeanObj.getAnswer());
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    while (rs.next()) {
                        securityQuestionId = rs.getLong("sp_UserSecurityQuestionId");
                    }
                }
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_saveaddress(NULL,'");
            myQuery.append(userAddrBean[0].getHouse());
            myQuery.append("','");
            myQuery.append(userAddrBean[0].getStreet());
            myQuery.append("','");
            myQuery.append(userAddrBean[0].getCity());
            myQuery.append("','");
            myQuery.append(userAddrBean[0].getState());
            myQuery.append("','");
            myQuery.append(userAddrBean[0].getPinCode());
            myQuery.append("','");
            myQuery.append(userAddrBean[0].getContact());
            myQuery.append("','");
            myQuery.append(userId);
            myQuery.append("','1','");
            myQuery.append(userAddrBean[0].getAddrTitle());
            myQuery.append("','");
            myQuery.append(userAddrBean[0].getAddrFirstName());
            myQuery.append("','");
            myQuery.append(userAddrBean[0].getAddrLastName());
            myQuery.append("','");
            myQuery.append(userAddrBean[0].getAddrMiddleName());
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    while (rs.next()) {
                        addressId = rs.getLong("sp_AddressId");
                    }
                }
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_saveaddress(NULL,'");
            myQuery.append(userAddrBean[1].getHouse());
            myQuery.append("','");
            myQuery.append(userAddrBean[1].getStreet());
            myQuery.append("','");
            myQuery.append(userAddrBean[1].getCity());
            myQuery.append("','");
            myQuery.append(userAddrBean[1].getState());
            myQuery.append("','");
            myQuery.append(userAddrBean[1].getPinCode());
            myQuery.append("','");
            myQuery.append(userAddrBean[1].getContact());
            myQuery.append("','");
            myQuery.append(userId);
            myQuery.append("','2','");
            myQuery.append(userAddrBean[1].getAddrTitle());
            myQuery.append("','");
            myQuery.append(userAddrBean[1].getAddrFirstName());
            myQuery.append("','");
            myQuery.append(userAddrBean[1].getAddrLastName());
            myQuery.append("','");
            myQuery.append(userAddrBean[1].getAddrMiddleName());
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    while (rs.next()) {
                        addressId = rs.getLong("sp_AddressId");
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return userId;
        }
    }

    public UserInfoBean getLoginPassword(String loginUsername) {
        userInfoBeanObj = new UserInfoBean();
        try {
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_selectLogin('");
            myQuery.append(loginUsername);
            myQuery.append("')");
//            myQuery.append("select user_id,username,password,role_id from users where username = '");
//            myQuery.append(loginUsername);
//            myQuery.append("'");
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            rs = dbc.executeQuery(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    userInfoBeanObj = setLoginPassword(rs);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return userInfoBeanObj;
        }
    }

    public String getPrefix(int prefixId) {
        String prefix = "";
        try {
            myQuery = new StringBuffer();
            myQuery.append("select title from title where title_id= '");
            myQuery.append(prefixId);
            myQuery.append("'");
            if (conn == null || conn.isClosed()) {
                conn = dbc.getConnection();
            }
            rs = dbc.executeQuery(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    while (rs.next()) {
                        prefix = rs.getString("title");
                    }
                }
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return prefix;
        }
    }

    public String getState(int stateId) {
        String state = null;        
        try {
            myQuery = new StringBuffer();
            myQuery.append("select state from state where state_id= '");
            myQuery.append(stateId);
            myQuery.append("'");
            if (conn == null || conn.isClosed()) {
                conn = dbc.getConnection();
            }
            rs = dbc.executeQuery(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    while (rs.next()) {
                        state = rs.getString("state");
                    }
                }
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return state;
        }
    }

    public boolean checkEmail(String emailId) {
        boolean emailExist = false;
        rs = null;
        try {
            myQuery = new StringBuffer();
            myQuery.append("select email from users where email = '");
            myQuery.append(emailId);
            myQuery.append("'");
            if (conn == null || conn.isClosed()) {
                conn = dbc.getConnection();
            }
            rs = dbc.executeQuery(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    while (rs.next()) {
                        emailExist = true;
                    }
                }
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return emailExist;
        }
    }

    public boolean checkUsername(String username) {
        boolean usernameExist = false;
        try {
            if (userAccUtil.checkSpecialChar(username) == false) {
                myQuery = new StringBuffer();
                myQuery.append("select username from users where username = '");
                myQuery.append(username);
                myQuery.append("'");
                if (conn == null || conn.isClosed()) {
                    conn = dbc.getConnection();
                }
                rs = dbc.executeQuery(conn, myQuery.toString());
                if (rs != null) {
                    String rsValue = String.valueOf(rs);
                    if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                        while (rs.next()) {
                            usernameExist = true;
                        }
                    }
                }
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return usernameExist;
        }
    }

    public Long userLog(Long userLogStatusId, Long userId, String ipaddress) {
        Long logStatusId = 0L;
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            if (ipaddress != null) {
                myQuery.append("CALL sp_saveuserlogstatus(NULL,'");
                myQuery.append(userId);
                myQuery.append("',NOW(),NULL,'");
                myQuery.append(ipaddress);
                myQuery.append("')");
                rs = dbc.executeProcedure(conn, myQuery.toString());
                if (rs != null) {
                    String rsValue = String.valueOf(rs);
                    if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                        while (rs.next()) {
                            logStatusId = rs.getLong("sp_UserLogStatusId");
                        }
                    }
                }
            } else {
                myQuery.append("CALL sp_saveuserlogstatus('");
                myQuery.append(userLogStatusId);
                myQuery.append("','");
                myQuery.append(userId);
                myQuery.append("',NULL,NOW(),NULL)");
                rs = dbc.executeProcedure(conn, myQuery.toString());

            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return logStatusId;
        }
    }

    public UserInfoBean getUserSecurityQuestion(String emailId) {
        userInfoBeanObj = new UserInfoBean();
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_forgotpassword('");
            myQuery.append(emailId);
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    userInfoBeanObj = setUserSecurityQuestion(rs);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return userInfoBeanObj;
        }
    }

    private UserInfoBean setUserSecurityQuestion(ResultSet rs) {
        try {
            while (rs.next()) {
                userInfoBeanObj = new UserInfoBean();
                userInfoBeanObj.setUserId(rs.getLong("user_id"));
                userInfoBeanObj.setSecurityQuestionId(rs.getLong("securityquestion_id"));
                userInfoBeanObj.setAnswer(rs.getString("answer"));
                userInfoBeanObj.setRoleId(rs.getLong("role_id"));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            return userInfoBeanObj;
        }

    }

    public boolean changePassword(Long userId, String encryptRandomPassword) {
        boolean passwordChanged = false;
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_changepassword('");
            myQuery.append(userId);
            myQuery.append("','");
            myQuery.append(encryptRandomPassword);
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                passwordChanged = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return passwordChanged;
        }
    }

    public UserInfoBean getUserInfo(Long userId, Long roleId) {
        userInfoBeanObj = new UserInfoBean();
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_selectUserinfo('");
            myQuery.append(userId);
            myQuery.append("','");
            myQuery.append(roleId);
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    userInfoBeanObj = setUserInfo(rs);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return userInfoBeanObj;
        }
    }

    private UserInfoBean setUserInfo(ResultSet rs) {
        userAccUtil = new UserAccountUtil();
        try {
            while (rs.next()) {
                userInfoBeanObj = new UserInfoBean();
                userInfoBeanObj.setUserId(rs.getLong("user_id"));
                userInfoBeanObj.setTitleId(rs.getLong("title_id"));
                userInfoBeanObj.setPrefix(rs.getString("title"));
                userInfoBeanObj.setFirstName(rs.getString("firstname"));
                userInfoBeanObj.setMiddleName(rs.getString("middlename"));
                userInfoBeanObj.setLastName(rs.getString("lastname"));
                userInfoBeanObj.setProfession(rs.getString("profession"));
                userInfoBeanObj.setEmailId(rs.getString("email"));
                userInfoBeanObj.setDateOfBirth(commUtil.formatDBDate(rs.getString("dateofbirth")));
                userInfoBeanObj.setMobileNo(rs.getString("mobileno"));
                userInfoBeanObj.setNewsLetter(rs.getInt("newsletter"));
                userInfoBeanObj.setAccountCreatedOn(commUtil.formatDBDate(rs.getString("accountcreatedon")));
                userInfoBeanObj.setAccountStatus(rs.getString("status"));
                userInfoBeanObj.setAccountStatusId(rs.getLong("status_id"));
                userInfoBeanObj.setFullName(rs.getString("title").concat(" ").concat(rs.getString("firstname")).concat(" ").concat(rs.getString("middlename")).concat(" ").concat(rs.getString("lastname")));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            return userInfoBeanObj;
        }
    }

    public boolean checkPassword(Long userId, String currentPassword, Long roleId) {
        boolean isPasswordExist = false;
        String dbEncryptedPassword = null, dbDecryptedPassword = null;
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_selectUserinfo('");
            myQuery.append(userId);
            myQuery.append("','");
            myQuery.append(roleId);
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    while (rs.next()) {
                        dbEncryptedPassword = rs.getString("password");
                    }
                }
            }
            dbDecryptedPassword = EncryptDecryptUtil.decrypt(dbEncryptedPassword);
            if (currentPassword.equals(dbDecryptedPassword)) {
                isPasswordExist = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return isPasswordExist;
        }
    }

    public UserAddressBean[] getUserAddress(Long userId) {
        UserAddressBean[] userAddrBean = null;
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_selectAddress('");
            myQuery.append(userId);
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    userAddrBean = setUserAddr(rs);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return userAddrBean;
        }
    }

    private UserAddressBean[] setUserAddr(ResultSet rs) {
        List alAddress = new ArrayList();
        int addressTypeId = 0;
        try {
            alAddress.clear();
            while (rs.next()) {
                addressTypeId = rs.getInt("addresstype_id");
                if (addressTypeId == 1) {
                    userAddrBeanObj = new UserAddressBean();
                    userAddrBeanObj.setAddressId(rs.getLong("address_id"));
                    userAddrBeanObj.setAddrTitleId(rs.getLong("title_id"));
                    userAddrBeanObj.setAddrTitle(rs.getString("title"));
                    userAddrBeanObj.setAddrFirstName(rs.getString("firstname"));
                    userAddrBeanObj.setAddrMiddleName(rs.getString("middlename"));
                    userAddrBeanObj.setAddrLastName(rs.getString("lastname"));
                    userAddrBeanObj.setHouse(rs.getString("house"));
                    userAddrBeanObj.setStreet(rs.getString("street"));
                    userAddrBeanObj.setCity(rs.getString("city"));
                    userAddrBeanObj.setPinCode(rs.getString("pincode"));
                    userAddrBeanObj.setStateId(rs.getLong("state_id"));
                    userAddrBeanObj.setState(rs.getString("state"));
                    userAddrBeanObj.setContact(rs.getString("contactno"));
                    userAddrBeanObj.setAddrFullName(rs.getString("title").concat(" ").concat(rs.getString("firstname")).concat(" ").concat(rs.getString("middlename")).concat(" ").concat(rs.getString("lastname")));
                    alAddress.add(userAddrBeanObj);
                } else if (addressTypeId == 2) {
                    userAddrBeanObj = new UserAddressBean();
                    userAddrBeanObj.setAddressId(rs.getLong("address_id"));
                    userAddrBeanObj.setAddrTitleId(rs.getLong("title_id"));
                    userAddrBeanObj.setAddrTitle(rs.getString("title"));
                    userAddrBeanObj.setAddrFirstName(rs.getString("firstname"));
                    userAddrBeanObj.setAddrMiddleName(rs.getString("middlename"));
                    userAddrBeanObj.setAddrLastName(rs.getString("lastname"));
                    userAddrBeanObj.setHouse(rs.getString("house"));
                    userAddrBeanObj.setStreet(rs.getString("street"));
                    userAddrBeanObj.setCity(rs.getString("city"));
                    userAddrBeanObj.setPinCode(rs.getString("pincode"));
                    userAddrBeanObj.setStateId(rs.getLong("state_id"));
                    userAddrBeanObj.setState(rs.getString("state"));
                    userAddrBeanObj.setContact(rs.getString("contactno"));
                    userAddrBeanObj.setAddrFullName(rs.getString("title").concat(" ").concat(rs.getString("firstname")).concat(" ").concat(rs.getString("middlename")).concat(" ").concat(rs.getString("lastname")));
                    alAddress.add(userAddrBeanObj);
                } else if (addressTypeId == 3) {
                    userAddrBeanObj = new UserAddressBean();
                    userAddrBeanObj.setAddressId(rs.getLong("address_id"));
                    userAddrBeanObj.setAddrTitleId(rs.getLong("title_id"));
                    userAddrBeanObj.setAddrTitle(rs.getString("title"));
                    userAddrBeanObj.setAddrFirstName(rs.getString("firstname"));
                    userAddrBeanObj.setAddrMiddleName(rs.getString("middlename"));
                    userAddrBeanObj.setAddrLastName(rs.getString("lastname"));
                    userAddrBeanObj.setHouse(rs.getString("house"));
                    userAddrBeanObj.setStreet(rs.getString("street"));
                    userAddrBeanObj.setCity(rs.getString("city"));
                    userAddrBeanObj.setPinCode(rs.getString("pincode"));
                    userAddrBeanObj.setStateId(rs.getLong("state_id"));
                    userAddrBeanObj.setState(rs.getString("state"));
                    userAddrBeanObj.setContact(rs.getString("contactno"));
                    userAddrBeanObj.setAddrFullName(rs.getString("title").concat(" ").concat(rs.getString("firstname")).concat(" ").concat(rs.getString("middlename")).concat(" ").concat(rs.getString("lastname")));
                    alAddress.add(userAddrBeanObj);
                }
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            return (UserAddressBean[]) alAddress.toArray(new UserAddressBean[0]);
        }
    }

    public UserAddressBean[] selectAddressByType(Long userId, int addressTypeId) {
        UserAddressBean[] userAddrBean = null;
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_selectaddressbytype('");
            myQuery.append(userId);
            myQuery.append("','");
            myQuery.append(addressTypeId);
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    userAddrBean = setSelectAddressByType(rs);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return userAddrBean;
        }
    }

    private UserAddressBean[] setSelectAddressByType(ResultSet rs) {
        List alAddress = new ArrayList();
        try {
            alAddress.clear();
            while (rs.next()) {
                userAddrBeanObj = new UserAddressBean();
                userAddrBeanObj.setAddressId(rs.getLong("address_id"));
                userAddrBeanObj.setAddrTitleId(rs.getLong("title_id"));
                userAddrBeanObj.setAddrFirstName(rs.getString("firstname"));
                userAddrBeanObj.setAddrMiddleName(rs.getString("middlename"));
                userAddrBeanObj.setAddrLastName(rs.getString("lastname"));
                userAddrBeanObj.setHouse(rs.getString("house"));
                userAddrBeanObj.setStreet(rs.getString("street"));
                userAddrBeanObj.setCity(rs.getString("city"));
                userAddrBeanObj.setPinCode(rs.getString("pincode"));
                userAddrBeanObj.setStateId(rs.getLong("state_id"));
                userAddrBeanObj.setContact(rs.getString("contactno"));
                alAddress.add(userAddrBeanObj);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            return (UserAddressBean[]) alAddress.toArray(new UserAddressBean[0]);
        }
    }

    public LinkedHashMap getTitleLHMap() {
        LinkedHashMap titleLHMap = new LinkedHashMap();
        myQuery = new StringBuffer();
        myQuery.append("select title_id, title from title");
        titleLHMap = commDAO.getData(myQuery.toString());
        return titleLHMap;
    }

    public LinkedHashMap getStateLHMap() {
        LinkedHashMap stateLHMap = new LinkedHashMap();
        myQuery = new StringBuffer();
        myQuery.append("select state_id, state from state");
        stateLHMap = commDAO.getData(myQuery.toString());
        return stateLHMap;
    }

    public LinkedHashMap getSecurityQuestionLHMap() {
        LinkedHashMap securityQuestionLHMap = new LinkedHashMap();
        myQuery = new StringBuffer();
        myQuery.append("SELECT 	securityquestion_id, securityquestion FROM securityquestion");
        securityQuestionLHMap = commDAO.getData(myQuery.toString());
        return securityQuestionLHMap;
    }

    private UserInfoBean setLoginPassword(ResultSet rs) {
        try {
            while (rs.next()) {
                userInfoBeanObj = new UserInfoBean();
                userInfoBeanObj.setPassword(rs.getString("password"));
                userInfoBeanObj.setUserId(rs.getLong("user_id"));
                userInfoBeanObj.setRoleId(rs.getLong("role_id"));
                userInfoBeanObj.setFullName(rs.getString("title").concat(" ").concat(rs.getString("firstname")).concat(" ").concat(rs.getString("lastname")));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            return userInfoBeanObj;
        }
    }

    public boolean saveNewsletter(Long userId, int newsLetter) {
        boolean newsletterChanged = false;
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_savenewsletter('");
            myQuery.append(userId);
            myQuery.append("','");
            myQuery.append(newsLetter);
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                newsletterChanged = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return newsletterChanged;
        }
    }

    public boolean saveAddress(Long addressId, UserAddressBean[] userAddrBeanList) {
        boolean addressSaved = false;
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_saveaddress('");
            myQuery.append(addressId);
            myQuery.append("','");
            myQuery.append(userAddrBeanList[0].getHouse());
            myQuery.append("','");
            myQuery.append(userAddrBeanList[0].getStreet());
            myQuery.append("','");
            myQuery.append(userAddrBeanList[0].getCity());
            myQuery.append("','");
            myQuery.append(userAddrBeanList[0].getState());
            myQuery.append("','");
            myQuery.append(userAddrBeanList[0].getPinCode());
            myQuery.append("','");
            myQuery.append(userAddrBeanList[0].getContact());
            myQuery.append("','0','0','");
            myQuery.append(userAddrBeanList[0].getAddrTitle());
            myQuery.append("','");
            myQuery.append(userAddrBeanList[0].getAddrFirstName());
            myQuery.append("','");
            myQuery.append(userAddrBeanList[0].getAddrLastName());
            myQuery.append("','");
            myQuery.append(userAddrBeanList[0].getAddrMiddleName());
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                addressSaved = true;
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return addressSaved;
        }
    }

    public boolean savePersonalInfo(Long userId, UserInfoBean userInfoBeanObj) {
        boolean personalInfoSaved = false;
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_saveuserinfo('");
            myQuery.append(userId);
            myQuery.append("','");
            myQuery.append(userInfoBeanObj.getPrefix());
            myQuery.append("','");
            myQuery.append(userInfoBeanObj.getFirstName());
            myQuery.append("','");
            myQuery.append(userInfoBeanObj.getMiddleName());
            myQuery.append("','");
            myQuery.append(userInfoBeanObj.getLastName());
            myQuery.append("','");
            myQuery.append(userInfoBeanObj.getProfession());
            myQuery.append("','");
            myQuery.append(userInfoBeanObj.getEmailId());
            myQuery.append("','");
            myQuery.append(userInfoBeanObj.getDateOfBirth());
            myQuery.append("','");
            myQuery.append(userInfoBeanObj.getMobileNo());
            myQuery.append("','1','");
            myQuery.append(userInfoBeanObj.getUserName());
            myQuery.append("','");
            myQuery.append(userInfoBeanObj.getPassword());
            myQuery.append("','1','2','");
            myQuery.append(userInfoBeanObj.getNewsLetter());
            myQuery.append("','");
            myQuery.append(userInfoBeanObj.getInfoSaveEditDate());
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                personalInfoSaved = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return personalInfoSaved;
        }
    }

    public UserInfoBean[] getAllUsers(long roleId) {
        UserInfoBean[] userList = null;
        userInfoBeanObj = new UserInfoBean();
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_selectUserinfo('0','");
            myQuery.append(roleId);
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    userList = setAllUsers(rs);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return userList;
        }
    }

    private UserInfoBean[] setAllUsers(ResultSet rs) {
        List alUser = new ArrayList();
        try {
            alUser.clear();
            while (rs.next()) {
                userInfoBeanObj = new UserInfoBean();
                userInfoBeanObj.setUserId(rs.getLong("user_Id"));
                userInfoBeanObj.setRoleId(rs.getLong("role_id"));
                userInfoBeanObj.setTitleId(rs.getLong("title_Id"));
                userInfoBeanObj.setPrefix(rs.getString("title"));
                userInfoBeanObj.setFirstName(rs.getString("firstname"));
                userInfoBeanObj.setMiddleName(rs.getString("middlename"));
                userInfoBeanObj.setLastName(rs.getString("lastname"));
                userInfoBeanObj.setProfession(rs.getString("profession"));
                userInfoBeanObj.setEmailId(rs.getString("email"));
                userInfoBeanObj.setDateOfBirth(commUtil.formatDBDate(rs.getString("dateofbirth")));
                userInfoBeanObj.setMobileNo(rs.getString("mobileno"));
                userInfoBeanObj.setNewsLetter(rs.getInt("newsletter"));
                userInfoBeanObj.setAccountStatus(rs.getString("status"));
                userInfoBeanObj.setAccountCreatedOn(commUtil.formatDBDate(rs.getString("accountcreatedon")));
                userInfoBeanObj.setFullName(rs.getString("title").concat(" ").concat(rs.getString("firstname")).concat(" ").concat(rs.getString("middlename")).concat(" ").concat(rs.getString("lastname")));
                alUser.add(userInfoBeanObj);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            return (UserInfoBean[]) alUser.toArray(new UserInfoBean[0]);
        }
    }

    public boolean deleteUser(Long userId) {
        boolean isUserDeleted = false;
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_deleteuserinfo('");
            myQuery.append(userId);
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_deleteuseraddress('");
            myQuery.append(userId);
            myQuery.append("')");
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                isUserDeleted = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return isUserDeleted;
        }
    }

    public UserInfoBean[] searchUser(String firstName, String lastName, String emailId, Long statusId) {
        UserInfoBean[] userInfoList = null;
        userInfoBeanObj = new UserInfoBean();
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("CALL sp_searchuser(");
            if (!firstName.equalsIgnoreCase("")) {
                myQuery.append("'");
                myQuery.append(firstName);
                myQuery.append("%',NULL,NULL,NULL)");
            } else if (!lastName.equalsIgnoreCase("")) {
                myQuery.append("NULL,'");
                myQuery.append(lastName);
                myQuery.append("%',NULL,NULL)");
            } else if (!emailId.equalsIgnoreCase("")) {
                myQuery.append("NULL,NULL,'");
                myQuery.append(emailId);
                myQuery.append("%',NULL)");
            } else if (statusId > 0) {
                myQuery.append("NULL,NULL,NULL,'");
                myQuery.append(statusId);
                myQuery.append("')");
            }
            rs = dbc.executeProcedure(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    userInfoList = setAllUsers(rs);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return userInfoList;
        }
    }

    public UserInfoBean[] getRoles() {
        UserInfoBean[] rolesList = null;
        userInfoBeanObj = new UserInfoBean();
        try {
            if (conn == null || conn.isClosed()) {
                conn = (Connection) dbc.getConnection();
            }
            myQuery = new StringBuffer();
            myQuery.append("SELECT 	`role_id`, 	`role`	 	FROM 	`retailws`.`role` 	WHERE `datastatus_id` != '3'");
            rs = dbc.executeQuery(conn, myQuery.toString());
            if (rs != null) {
                String rsValue = String.valueOf(rs);
                if (!rsValue.equalsIgnoreCase("Result set representing update count of 0")) {
                    rolesList = setRoles(rs);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                dbc.freeResultSet(rs);
                dbc.freeConnection(conn);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return rolesList;
        }
    }

    private UserInfoBean[] setRoles(ResultSet rs) {
        List alRoles = new ArrayList();
        try {
            alRoles.clear();
            while (rs.next()) {
                userInfoBeanObj = new UserInfoBean();
                userInfoBeanObj.setRoleId(rs.getLong("role_id"));
                userInfoBeanObj.setRole(rs.getString("role"));
                alRoles.add(userInfoBeanObj);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            return (UserInfoBean[]) alRoles.toArray(new UserInfoBean[0]);
        }

    }
}
